﻿using NLog;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Fishing.Model;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace Fishing.Dal
{
    public class AdminRoleDal
    {
        private static readonly Logger _logger = LogManager.GetCurrentClassLogger();


        /// <summary>
        /// ConStr
        /// </summary>
        private static readonly string ConStr = ConfigurationManager.ConnectionStrings["_fishingdb"].ConnectionString;

        public bool InsertDate(admin_role role)
        {

            var ret = false;

            const string sql = "insert into admin_role (roleID,roleName,title,`order`,createTime,createBy) values " +
                               "(@roleID,@roleName,@title,@order,now(),@createBy)";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            command.Parameters.Add("@roleID", MySqlDbType.VarChar, 10).Value = role.roleID;
            command.Parameters.Add("@roleName", MySqlDbType.VarChar, 10).Value = role.roleName;
            command.Parameters.Add("@title", MySqlDbType.VarChar, 100).Value = role.title;
            command.Parameters.Add("@order", MySqlDbType.Int32).Value = role.order;
            command.Parameters.Add("@createBy", MySqlDbType.VarChar, 10).Value = role.createBy;
            try
            {
                connection.Open();
                ret = Convert.ToBoolean(command.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("InsertDate ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();

            }
            return ret;
        }

        public bool UpdateDate(admin_role role)
        {

            var ret = false;

            const string sql = "UPDATE admin_role set roleName=@roleName , title=@title,updateTime=now(),updateBy=@updateBy where roleID=@roleID";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            command.Parameters.Add("@roleID", MySqlDbType.VarChar, 10).Value = role.roleID;
            command.Parameters.Add("@roleName", MySqlDbType.VarChar, 10).Value = role.roleName;
            command.Parameters.Add("@title", MySqlDbType.VarChar, 100).Value = role.title;
            command.Parameters.Add("@updateBy", MySqlDbType.VarChar, 20).Value = "";
            try
            {
                connection.Open();
                ret = Convert.ToBoolean(command.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("UpdateDate ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();

            }
            return ret;
        }

        public bool DelDate(string roleID)
        {

            var ret = false;

            if (!IsRoleByUser(roleID))
            {
                const string sql =
                    "delete FROM admin_role where roleID=@roleID";

                var connection = new MySqlConnection(ConStr);
                var command = new MySqlCommand
                {
                    Connection = connection,
                    CommandText = sql,
                    CommandType = CommandType.Text
                };
                command.Parameters.Add("@roleID", MySqlDbType.VarChar, 10).Value = roleID;
                try
                {
                    connection.Open();
                    ret = Convert.ToBoolean(command.ExecuteNonQuery());
                }
                catch (Exception ex)
                {
                    _logger.Error("======================================================");
                    _logger.Error("DelDate ERROR !");
                    _logger.Error("ERROR Message : " + ex.Message);
                    _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                    _logger.Error("======================================================");
                }
                finally
                {
                    connection.Close();

                }
            }

            return ret;
        }



        public int GetNewOrder()
        {
            int result = 0;
            const string sql = "select IFNULL(MAX(`order`),0) from  admin_role";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            try
            {
                connection.Open();
                result = Convert.ToInt32(command.ExecuteScalar());
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("GetNewOrder ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();

            }

            return result + 1;
        }

        public bool IsRoleByUser(string roleID)
        {
            var ret = false;
            const string sql = "select userRoleID from admin_user_role where roleID = @roleID ";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            command.Parameters.Add("@roleID", MySqlDbType.VarChar, 10).Value = roleID;
            try
            {
                connection.Open();
                if (command.ExecuteScalar() != null)
                {
                    ret = true;
                }
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("IsRoleByUser ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();

            }

            return ret;
        }

        public List<admin_role> GetRoleDataBy(string where)
        {
            var retList = new List<admin_role>();
            var sql = "select * from admin_role where 1=1 ";
            if (where.Length > 0)
            {
                sql = sql + where;
            }

            sql = sql + " order by `order` asc";

            var connection = new MySqlConnection(ConStr);
            var command = new MySqlCommand
            {
                Connection = connection,
                CommandText = sql,
                CommandType = CommandType.Text
            };
            try
            {
                connection.Open();
                using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            retList.Add(new admin_role()
                            {
                                roleID = reader["roleID"].ToString(),
                                roleName = reader["roleName"].ToString(),
                                title = reader["title"].ToString()
                            });
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _logger.Error("======================================================");
                _logger.Error("GetRoleDataBy ERROR !");
                _logger.Error("ERROR Message : " + ex.Message);
                _logger.Error("ERROR StackTrace : " + ex.StackTrace);
                _logger.Error("======================================================");
            }
            finally
            {
                connection.Close();
            }
            return retList;

        }


    }
}